package org.dromara.community.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.dromara.community.entity.AsCommunityAnnouncements;
import org.dromara.community.entity.dto.AsCommunityAnnouncementsDTO;

import java.util.List;

@Mapper
public interface AsCommunityAnnouncementsMapper extends BaseMapper<AsCommunityAnnouncements> {

    @Select("SELECT\n" +
        "    a.id,\n" +
        "    a.category,\n" +
        "    a.subcategory,\n" +
        "    a.title,\n" +
        "    a.description,\n" +
        "    a.tag,\n" +
        "    GROUP_CONCAT(i.url SEPARATOR ',') AS url,\n" +
        "    a.created_by AS createdBy,\n" +
        "    a.created_time AS createdTime,\n" +
        "    a.updated_by AS updatedBy,\n" +
        "    a.updated_time AS updatedTime,\n" +
        "    a.is_delete AS isDelete\n" +
        "FROM\n" +
        "    as_community_announcements AS a\n" +
        "    LEFT JOIN as_notice_image AS i ON a.id = i.image_id\n" +
        "WHERE\n" +
        "    i.image_id = #{image_id}\n" +
        "GROUP BY\n" +
        "    a.id,\n" +
        "    a.category,\n" +
        "    a.subcategory,\n" +
        "    a.title,\n" +
        "    a.description,\n" +
        "    a.tag,\n" +
        "    a.created_by,\n" +
        "    a.created_time,\n" +
        "    a.updated_by,\n" +
        "    a.updated_time,\n" +
        "    a.is_delete;\n")
    List<AsCommunityAnnouncementsDTO> selectAnnouncementByImageId(Integer imageId);
}
